﻿CREATE DATABASE KBManagement
GO 
USE KBManagement
GO
CREATE TABLE Category
( 
	ID NVARCHAR(5) PRIMARY KEY,
	[Name] NVARCHAR(100) NOT NULL ,
	Status BIT DEFAULT 1 NOT NULL
)
GO
CREATE TABLE Product
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	[Name] NVARCHAR(100) UNIQUE NOT NULL ,
	Price MONEY NOT NULL ,
	ImageUsePath NVARCHAR(500) NOT NULL ,
	ImageNotUsePath NVARCHAR(500) NOT NULL ,
	CategoryID NVARCHAR(5) REFERENCES Category(ID) ,
	Status BIT DEFAULT 1 NOT NULL,
)
GO
CREATE TABLE FoodCaterory
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	[Name] NVARCHAR(200) UNIQUE NOT NULL ,
	[Status] BIT 	
)


GO

CREATE TABLE Store
(
	ID NVARCHAR(50) PRIMARY KEY,
	S_Name NVARCHAR(50),
	--So luong thuc te
	Quantity FLOAT,
	warning NVARCHAR(200) DEFAULT('Xin mời nhập hàng vào kho'),--Moi them
	Unit NVARCHAR(20),
	--So luong san pham ma trong kho can duy tri
	QuantityStandard INT DEFAULT 1 --Moi them
)
GO


CREATE TABLE StoreTemp
(
	ID NVARCHAR(50) PRIMARY KEY,
	--So luong thuc te
	Quantity FLOAT,
	Warning NVARCHAR(200),--Moi them
	StoID NVARCHAR(50) FOREIGN KEY REFERENCES Store (ID) UNIQUE
)
GO

CREATE TABLE Food
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Fo_Name NVARCHAR(200) UNIQUE NOT NULL ,
	Price money DEFAULT(0) NOT NULL ,
    [Status] BIT DEFAULT 1 NOT NULL,
	FoodCategoryID INT FOREIGN KEY REFERENCES FoodCaterory(ID)
)
--Moi them

CREATE TABLE FoodComponent
(
	ComId INT IDENTITY(1,1) PRIMARY KEY,
	FoId INT FOREIGN KEY REFERENCES Food(ID),
	StoreTempId NVARCHAR(50) FOREIGN KEY REFERENCES StoreTemp(ID),
	ComQuantilty FLOAT,
	ComDescription NVARCHAR(500)
)

--End
GO
CREATE TABLE Customer_Type
(
	ID NVARCHAR(3) PRIMARY KEY,
	Ct_Name NVARCHAR(100) UNIQUE NOT NULL ,
	Rate INT DEFAULT 1 NOT NULL,
	NormsMoney MONEY,
	Status BIT DEFAULT 1 NOT NULL
)
GO
CREATE TABLE Customer
(
	ID INT IDENTITY(100000,1) PRIMARY KEY,
	FullName NVARCHAR(200) NOT NULL ,
	BirthDay DATETime,
	Gender VARCHAR(20),
	Email NVARCHAR(500) NOT NULL,
	Adddress NVARCHAR(500) NULL,
	Phone NVARCHAR(50) NULL,
	CustomeTypeID NVARCHAR(3) REFERENCES Customer_Type(ID),
	Status BIT DEFAULT 1 NOT NULL,
	TotalMoney MONEY
)
GO
CREATE TABLE Purchase_Order
(
	ID INT IDENTITY(100000,1) PRIMARY KEY,
	CustomerID INT REFERENCES Customer(ID),
	ProductID INT REFERENCES Product(ID),
	StartTime DATETIME,
	EndTime DATETIME,
	Sale_Rate INT DEFAULT 0 NOT NULL,
	Purchase_Date DATETIME DEFAULT GETDATE(),
	Total_Amount MONEY,	
	Purchase_Amount MONEY,
	[Status] BIT DEFAULT 0	
)
GO
CREATE TABLE Order_Detail
(
	ID INT IDENTITY(100000,1) PRIMARY KEY,
	OrderID INT REFERENCES Purchase_Order(ID),
	FoodID INT REFERENCES Food(ID),
	Quantity INT DEFAULT 1 NOT NULL,	
	Amount MONEY	
)
GO



CREATE TABLE Store_Log
(
	ID NVARCHAR(50) PRIMARY KEY, 
	TransferDate DATETIME,
	Quantity Float,
	Price MONEY,
	TypeIE BIT,
	StoreID NVARCHAR(50) FOREIGN KEY REFERENCES Store(ID)
)
GO
CREATE TABLE [Role]
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	F_Name NVARCHAR(50) NOT NULL UNIQUE,
	Status BIT
)
GO
CREATE TABLE Admin
(
	ID INT IDENTITY(1,1) PRIMARY KEY, 
	UserName NVARCHAR(50) NOT NULL UNIQUE,
	Pass NVARCHAR(50) NOT NULL,
	RoleID INT FOREIGN KEY REFERENCES [Role](ID),
	Status BIT
)
GO
CREATE TABLE Position
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Ps_Name NVARCHAR(50) NOT NULL UNIQUE
)
GO
CREATE TABLE TimeSlot
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Ts_Name NVARCHAR(50) NOT NULL UNIQUE,
	StartTime NVARCHAR(50) NOT NULL,
	EndTime NVARCHAR(50) NOT NULL,
)
GO
CREATE TABLE Employee
(
	ID NVARCHAR(9) PRIMARY KEY,
	FullName NVARCHAR (200) NOT NULL,
	BirthDay  smalldatetime NOT NULL,
	Gender NVARCHAR(10) NOT NULL,
	Address NVARCHAR(500) NOT NULL,
	Phone NVARCHAR(50),
	Email NVARCHAR(100),
	--The tin dung neu co
	CreditCard NVARCHAR(20),--Moi them
	Salary MONEY,
	PositionID INT FOREIGN KEY REFERENCES Position(ID),
	TimeSlotID INT FOREIGN KEY REFERENCES TimeSlot(ID),
	Experience NVARCHAR(MAX) NULL,
	Status BIT
)
GO

CREATE TABLE Pay_Salary
(
	PsId INT IDENTITY(1,1) PRIMARY KEY,
	PayMonth Int not null,
	PayYear Int Not null,
	EmployeeID NVARCHAR(9) FOREIGN KEY REFERENCES Employee(ID),
	--Tổng tiền phạt trong tháng
	TotalPenaltyMoney MONEY,--Moi them
	--Tổng tiền thưởng trong tháng
	TotalRewardMoney MONEY,--Moi them
	--Tổng tiền boa được nhận trong tháng
	TotalTipMoney MONEY,--Moi them
	TotalAmount MONEY ,
	CashAdvance Money,
	RetainMoney Money ,
	Status BIT,
	unique(PayYear,PayMonth,EmployeeID),
	check (PayMonth>0 and PayMonth<13)
)
GO

CREATE TABLE Penalty
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Pe_Name NVARCHAR(50) NOT NULL UNIQUE,
	ExceptSalary MONEY,
	Status BIT
)
GO
CREATE TABLE Penalty_Detail
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	PsId Int FOREIGN KEY REFERENCES Pay_Salary(PsId),
	PenaltyID INT FOREIGN KEY REFERENCES Penalty(ID),
	Description NVARCHAR(MAX) NULL,
	PenaltyDate DATETIME,
	Status BIT --tinh trang da Tru hay chua
)

GO

CREATE TABLE Reward
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Reason NVARCHAR(MAX),
	Amount MONEY,
	Status BIT,--tinh trang da thuong hay chua
	PsId Int FOREIGN KEY REFERENCES Pay_Salary(PsId),
	RewardDate DATETIME
)
GO
CREATE TABLE Cash_Advance
(
	Ca_ID INT PRIMARY KEY IDENTITY(1,1),
	Description NVARCHAR(500),
	Amount money ,
	PsId Int references Pay_Salary(PsId)
)
--End

GO
CREATE TABLE Transaction_Log
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Description NVARCHAR(MAX),
	LogTime DATETIME NOT NULL,
	[Type] INT,
)


--Moi them
--Bảng lưu tổng dư của quỹ
GO
CREATE TABLE funds_general
(
	balance money
)
GO
--End
--Moi them
--Thông tin % tiền Tip khách cho nhân viên
CREATE TABLE Percent_Tip
(
	Pt_Id INT IDENTITY(1,1) PRIMARY KEY,
	Tip_Percent_Receive FLOAT
)
GO
--Moi them
CREATE TABLE Tip
(
	Tip_Id INT IDENTITY(1,1) PRIMARY KEY,
	Tip_Money MONEY,
	Tip_Date DATETIME,
	Tip_Money_Receive MONEY,
	Tip_Balance_Money MONEY,
	PsId Int FOREIGN KEY REFERENCES Pay_Salary(PsId)
)
--end
GO
--Moi them2
CREATE TABLE CategoryExtraFees
(
	CEF_ID INT IDENTITY(1,1) PRIMARY KEY,
	CEF_Name NVARCHAR(30) NOT NULL
)
GO
CREATE TABLE ExtraFees
(
	EF_ID INT IDENTITY(1,1) PRIMARY KEY,
    EF_Name NVARCHAR(1000),
	EF_MONEY MONEY NOT NULL ,
	CEF_Status BIT,
	CEF_ID INT REFERENCES CategoryExtraFees(CEF_ID),
	EF_Month int,
	EF_Year int,
)

--end